import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



/*
 * @Sangeeta
 * @Uses:To prune tuples in fid_bugid table such that  status="closed", and resolution="done".
 * @Uses: As so many files like .xml, .classpath are generally used for feature request or other things. 
 * */

public class Prune_fid_bugid 
{
    public Connection conn=null;
    public String user="root";
    public String pass="123";
    public String db="jboss";
	
	
	public void prune()
	{
		String query ="select * from fid_bugid ";
		Statement stmt=null;
		
		
		try 
		{
			 stmt= conn.createStatement();
			 stmt.execute(query);
		     ResultSet rs= stmt.getResultSet();
		    
		     Statement s2=null;
    		 s2=conn.createStatement();
		     
		     while(rs.next())
		     {
		    	 try
		    	 {
		    		 String bugid=rs.getString("bugid");
		    		 String q2="Select * from bug_svn_info where bugid='"+bugid+"'";
		    		
		    		 s2.execute(q2);
		    		 ResultSet rs2= s2.getResultSet();
		    	 
		    		 String type="";
		    		 String status="";
		    		 String resolution="";
		    		 	while(rs2.next())
		    		 		{
		    		 			type=rs2.getString("type");
		    		 			status=rs2.getString("status");
		    		 			resolution=rs2.getString("resolution");
		    			
		    		 			//if(type.equalsIgnoreCase("bug")&&(status.equalsIgnoreCase("closed"))&&resolution.equalsIgnoreCase("Duplicate Issue"))
		    		 			if((status.equalsIgnoreCase("closed"))&&resolution.equalsIgnoreCase("Done"))
			    		 		  {
		    		 				String fid=rs.getString("fid");
		    		 				String svnid=rs.getString("svnid");
		    			  	
		    		 				Statement s3=null;
		    		 				s3=conn.createStatement();
		    		 				
		    		 				//check if the file_contains only// @Not contain currently 
		    			  	
		    		 				String insert="insert into new_fid_bugid values('"+fid+"','"+bugid+"','"+svnid+"')";
		    		 				s3.executeUpdate(insert);
		    			  	
		    		 				if(s3!=null)
		    		 					s3.close();
		    		 			}//if
		    		 		}//while
		    		 
		    		 	if(rs2!=null)
		    		 		rs2.close();
		    		 
		    	 }
		    	 catch (SQLException e) 
		    	  { e.printStackTrace(); 	}
		    	 
		    	 
		     }//while
		     
		
		 if(s2!=null)
			 s2.close();
			 
		} catch (SQLException e) 
		{ e.printStackTrace(); 	}
		
		if(stmt!=null)
		{try {
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}}
	}//prune
		
	public static void main(String args[])
	{
	   Prune_fid_bugid pfb =  new Prune_fid_bugid();
	   db_conn db= new db_conn();
	   pfb.conn=db.connect(pfb.db,pfb.user , pfb.pass);
	   
	   pfb.prune();
	   db.close(pfb.conn);
	   
	   
	}//main
	
}//class
